/*
 * DatabaseTransactionHandler.java
 */

package nxtgeninventorysystem.Model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import nxtgeninventorysystem.Data.Item;
import nxtgeninventorysystem.Data.ItemSearchOptions;
import nxtgeninventorysystem.Data.Person;
import nxtgeninventorysystem.Data.PersonSearchOptions;
import nxtgeninventorysystem.Data.Transaction;
import nxtgeninventorysystem.Data.TransactionSearchOptions;

/**
 * The DatabaseTransactionHandler class provides general database
 * functionalities to the Person entity including functionalities regarding
 * some of its attributes.
 * @author Seraph Softworks
 */
public final class DatabaseTransactionHandler {

    public DatabaseTransactionHandler() {

    }

    public Person getTransactionPerson(int trans_id) {
        Person person = new Person();
        ResultSet result = Database.executeQuery("select distinct person.* from transaction, person where person.id = transaction.person and transaction.id = " + trans_id);
        try {
            if(result.next()) {
                person.setId(result.getInt("id"));
                person.setName(result.getString("name"));
                person.setContactNo(result.getString("contact_no"));
                person.setTeam(result.getString("team"));
                return person;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseTransactionHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return null;
    }

    public ArrayList <Item> getTransactionItems(int trans_id, ItemSearchOptions order, boolean ascending) {
        String asc = "";
        if(order.itemOrderSpecified()) {
            if(ascending)
                asc = "asc";
            else
                asc = "desc";
        }

        ArrayList<Item> collection = new ArrayList<Item>();
        ResultSet result = Database.executeQuery("select distinct item.* from item, transactionitem, transaction where item.id = transactionitem.item and transactionitem.transaction = transaction.id and transaction.id = " + trans_id + order.getItemOrderString() + asc);
        try {
            while(result.next()) {
                Item item = new Item();
                item.setId(result.getInt("id"));
                item.setName(result.getString("name"));
                item.setPerson(Database.person.readPerson(result.getInt("person")));
                item.setDeleted(result.getBoolean("deleted"));
                item.setDescription(result.getString("description"));
                item.setType(result.getString("type"));
                collection.add(item);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseTransactionHandler.class.getName()).log(Level.SEVERE, null, ex);
        }

        return collection;
    }

    public int createTransaction(Transaction transaction) {

        int id = 0;

        if(transaction == null)
            return 0;

        ArrayList <String> cmds = new ArrayList <String> ();

        //create transaction
        String tquery = "insert into transaction (type, date, purpose, person) values(";
        tquery += "'" + transaction.getType().replace("\'","\\'").replace("\"", "\\\"") + "',";
        tquery += "NOW(),";
        tquery += "'" + transaction.getPurpose().replace("\'","\\'").replace("\"", "\\\"") + "',";
        tquery += "'" + transaction.getPerson().getId() + "');";

        if(! Database.executeUpdate(tquery)) {
            return 0;
        }
        else {
            ResultSet rst = Database.executeQuery("select LAST_INSERT_ID() as 'last'");
            try {
                if (rst.next()) {
                     id = rst.getInt("last");
                }
            } catch (SQLException ex) {
                Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        //create transaction items
        if(transaction.getItems() != null) {
            for(Item item : transaction.getItems()) {
                String iquery = "insert into transactionitem (transaction, item) values(";
                iquery += "" + id + ",";
                iquery += "'" + item.getId() + "');";
                cmds.add(iquery);
            }
        }

        String[] cmdslist = new String[cmds.size()];
        for(int i = 0 ; i < cmds.size() ; i++) {
            cmdslist[i] = cmds.get(i);
        }

        Database.executeBatch(cmdslist);

        return id;
    }

    public ArrayList<Transaction> searchTransaction(Transaction values, TransactionSearchOptions options, TransactionSearchOptions order, boolean ascending) {
        try {
            String query = "select distinct transaction.* from transaction, item, person, transactionitem, (select transaction.id as 'transaction', COUNT(*) as 'items' from transaction, transactionitem where transactionitem.transaction = transaction.id group by transaction.id) counts ";
            String _order = "";
            if (ascending) {
                _order = "asc";
            } else {
                _order = "desc";
            }

            if(options.transactionOptionsSpecified())
                query += "where ";

            ArrayList<String> conditions = values.getTransactionSearchParametersQueryStrings(options);

            for (int i = 0; i < conditions.size(); i++) {
                query += conditions.get(i);
                if (i < conditions.size() - 1) {
                    query += " and ";
                }
            }

            if(order != null) {
                query += order.getTransactionOrderString();
                if(order.transactionOrderSpecified())
                    query += _order;
            }

            ArrayList<Transaction> collection = new ArrayList<Transaction>();

            System.err.println(query);

            ResultSet result = Database.executeQuery(query);

            while(result.next()){
                Transaction transaction = new Transaction();
                transaction.setId(result.getInt("id"));
                transaction.setType(result.getString("type"));
                transaction.setDate(result.getDate("date"));
                transaction.setPurpose(result.getString("purpose"));
                transaction.setPerson(Database.person.readPerson(result.getInt("person")));
                transaction.setItems(Database.transaction.getTransactionItems(result.getInt("id"), new ItemSearchOptions(), true));
                collection.add(transaction);
            }
            return collection;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Transaction readTransaction(int transaction_id) {
         try {
            Transaction transaction = new Transaction();
            ResultSet result = Database.executeQuery("select distinct * from transaction where id = " + transaction_id);

            if(result.next()){
                transaction.setId(result.getInt("id"));
                transaction.setType(result.getString("type"));
                transaction.setDate(result.getDate("date"));
                transaction.setPurpose(result.getString("purpose"));
                transaction.setPerson(Database.person.readPerson(result.getInt("person")));
                transaction.setItems(Database.transaction.getTransactionItems(result.getInt("id"), new ItemSearchOptions(), true));
            }
            else {
                return null;
            }

            return transaction;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseItemHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

}
